﻿CREATE PROCEDURE [dbo].[proc_Project_Getlist_xzys]
(
    @CompanyId int,
	@StartDate nvarchar(10),
	@EndDate nvarchar(10),	
	@StartIndex int,
	@EndIndex INT,
	@noe_flag INT
)

AS
--	;WITH list As(


----SELECT ROW_NUMBER() OVER (ORDER BY Id)AS Row,
----CusName,(YingShouPrice-ShiShouPrice) AS iac
----,OId
----,CusName AS CompanyName
----FROM [Order]  Where  YingShouPrice>ShiShouPrice AND 
----OId in(Select Distinct OrderId From Project Where CompanyId=@companyId 
----and  Convert(nchar(10),CreateDate,120) BETWEEN @StartDate and @EndDate)
--	SELECT ROW_NUMBER() OVER (ORDER BY OId)AS Row,* FROM ( SELECT SUM(iac) iac,CompanyName,OId FROM (
--	Select id,(YingShouPrice-ShiShouPrice) AS iac,CusName AS CompanyName,OId
--	  From  [Order] Where CompanyId=@CompanyId and Convert(nchar(10),AuditDate,120) BETWEEN @StartDate and @EndDate
--	   and YingShouPrice<>ShiShouPrice AND StateId>=1 AND (NOE_Flag<>2)
--	 -- AND (NOE_Flag<>2) AND 	  oid IN (SELECT oid FROM [Order] o WHERE companyId=@CompanyId AND o.NOE_Flag<>2 )
	  
--	  UNION 
	  
--	  SELECT 0,dch_ysh AS iac,CusName AS CompanyName,OId
--	    FROM Order_Change_Hedge WHERE CompanyId=@CompanyId and Convert(nchar(10),UpTime,120) 
--	  between @StartDate and @EndDate AND dch_ysh !=0 
--	   AND  oid IN (SELECT oid FROM [Order] o WHERE companyId=@CompanyId AND o.NOE_Flag<>2 )
--	 ) AS a GROUP BY CompanyName,OId ) AS nb
--	)
	
--	Select *,(SELECT COUNT(1) FROM list) AS DataCount,(SELECT SUM(IAC) FROM list) AS DataSum From list Where Row between @StartIndex and @EndIndex  

Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime, @StartDate + ' 00:00:00')
	Set @eRq = Convert(Datetime, @EndDate + ' 23:59:59')
	
	;WITH list As(


--SELECT ROW_NUMBER() OVER (ORDER BY Id)AS Row,
--CusName,(YingShouPrice-ShiShouPrice) AS iac
--,OId
--,CusName AS CompanyName
--FROM [Order]  Where  YingShouPrice>ShiShouPrice AND 
--OId in(Select Distinct OrderId From Project Where CompanyId=@companyId 
--and  Convert(nchar(10),CreateDate,120) BETWEEN @StartDate and @EndDate)
	SELECT ROW_NUMBER() OVER (ORDER BY createdate)AS Row,* FROM 
	( SELECT SUM(iac) iac,CompanyName,OId,createdate,YingShouPrice FROM (
	Select id,h_yingshou AS iac,CusName AS CompanyName,OId,createdate,YingShouPrice-ShiShouPrice as YingShouPrice
	  From  [Order] Where CompanyId=@CompanyId and financerenlingdate>= @sRq and financerenlingdate<=@eRq
	   and h_yingshou>0
	   --YingShouPrice<>ShiShouPrice --2014-12-30取消，查询发生额
	   AND StateId>=1 AND (NOE_Flag<>2)
	 -- AND (NOE_Flag<>2) AND 	  oid IN (SELECT oid FROM [Order] o WHERE companyId=@CompanyId AND o.NOE_Flag<>2 )
	  
	  UNION all
	  
	  SELECT 0,dch_ysh AS iac,Order_Change_Hedge.CusName AS CompanyName,Order_Change_Hedge.OId,uptime,o.YingShouPrice-o.ShiShouPrice as YingShouPrice
	    FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid =o. oid and Order_Change_Hedge.CompanyId=@CompanyId and UpTime>=@sRq and UpTime<=@eRq AND dch_ysh !=0 
	   AND o.companyId=@CompanyId AND o.NOE_Flag<>2 
	 ) AS a GROUP BY CompanyName,OId,createdate,YingShouPrice ) AS nb
	)
	
	Select *,(SELECT COUNT(1) FROM list) AS DataCount,(SELECT SUM(IAC) FROM list) AS DataSum From list Where Row between @StartIndex and @EndIndex  

	
	RETURN
